C:/Projekte/RAIS/dataaccesslayer/TableManagement.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Xml;

using RAIS.Common.DynamicMaskManagement;
using RAIS.Common.TableManagement;

using TableTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Table.TableTypeData>;
using FieldTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Field.FieldTypeData>;
using FieldSizes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Field.FieldSizeData>;
using NecessityTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Field.NecessityTypeData>;
using TableGroups = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.TableGroup>;
using Tables = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Table>;
using Fields = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Field>;
using QueryTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Query.QueryTypeData>;
using Queries = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Query>;
using QueryParameterTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.QueryParameter.QueryParameterTypeData>;
//using QueryParameterTypes = System.Collections.Generic.Dictionary<RAIS.Common.TableManagement.QueryParameter.QueryParameterType, RAIS.Common.TableManagement.QueryParameter.QueryParameterTypeData>;
using QueryParameters = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.QueryParameter>;

namespace RAIS.DataAccessLayer
{
    public class TableManagement
        {
                //*********************************************************************
                #region Properties

        public static FieldTypes FieldTypes
        {
            get { return GetFieldTypes(); }
        }
        public static FieldSizes FieldSizes
        {
            get { return GetFieldSizes(); }
        }
                public static NecessityTypes NecessityTypes
                {
                        get { return GetNecessityTypes(); }
                }
        public static TableTypes TableTypes
        {
            get { return GetTableTypes(); }
        }
        public static TableGroups TableGroups
        {
            get { return GetTableGroups(); }
        }
                public static Tables AllTables
                {
                        get { return GetAllTables(); }
                }
                public static QueryTypes QueryTypes
                {
                        get { return GetQueryTypes(); }
                }
                public static QueryParameterTypes QueryParameterTypes
                {
                        get { return GetQueryParameterTypes(); }
                }
                #endregion
                //*********************************************************************
                #region Constructor
                static TableManagement()
        {
                        Table.TableTypesDictionary = GetTableTypes();
                        Field.FieldTypesDictionary = GetFieldTypes();
                        Field.FieldSizesDictionary = GetFieldSizes();
                        Field.NecessityTypesDictionary = GetNecessityTypes();
                        Query.QueryTypesDictionary = GetQueryTypes();
                        QueryParameter.QueryParameterTypesDictionary = GetQueryParameterTypes();

                        if (DynamicMask.DynamicMaskTypesDictionary == null)
                                new DynamicMaskManagement();
                }
        #endregion
                //*********************************************************************
                #region Public static methods
        public static bool IsEmptyFelds(string tableName, string fieldName)
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                {
                    sqlConnection.Open();

                    using (SqlCommand sqlCommand = new SqlCommand(string.Format(Constants.QUERY_TEXT_HAS_ROWS, tableName), sqlConnection))
                    {
                        object result = sqlCommand.ExecuteScalar();
                        return !(result is DBNull) && ((int)result) == 0;
                    }
                }
            }
            catch
            {
                return false;
            }
        }
                public static bool SaveTableGroup(TableGroup tableGroup, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateTableGroup, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableGroupID, tableGroup.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Accepted, tableGroup.Accepted);
                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.TABLE_GROUP_ADDED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static Tables GetCustomTablesByTableGroup(TableGroup tableGroup)
                {
                        Tables tables = new Tables();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTablesByTableGroupID, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableGroupID, tableGroup.ID);
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CustomTable, 1);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Table table = new Table(sqlReader);
                                                        table.Fields = GetFieldsByTable(table);
                                                        tables.Add(table.Name, table);
                                                }
                                        }
                                }
                        }
                        return tables;
                }
                public static Tables GetTablesByTableGroup(TableGroup tableGroup, bool showProtectors, bool showEvaluators)
                {
                        Tables tables = new Tables();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTablesByTableGroupID, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableGroupID, tableGroup.ID);
                                        //if (designMode)
                                        //    DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Hidden, null);
                                        if (showProtectors)
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ShowProtectors, 1);
                                        if (showEvaluators)
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ShowEvaluators, 1);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Table table = new Table(sqlReader);
                                                        table.Fields = GetFieldsByTable(table);
                                                        tables.Add(table.Name, table);
                                                }
                                        }
                                }
                        }
                        return tables;
                }
                public static Tables GetAllTables()
                {
                        Tables tables = new Tables();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                // Call usp_GetTablesByTableGroupID without parameters
                                // stored procedure returns all tables for all groups in this case
                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTablesByTableGroupID, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Table table = new Table(sqlReader);
                                                        table.Fields = GetFieldsByTable(table);
                                                        tables.Add(table.Name, table);
                                                }
                                        }
                                }
                        }
                        return tables;
                }
        public static Table GetTableByTableID(int id, SqlConnection sqlConnection)
        {
            Table table = new Table();

            using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTableByTableID, sqlConnection))
            {
                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, id);

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                {
                    sqlReader.Read();

                    if (sqlReader.HasRows)
                    {
                        table = new Table(sqlReader);
                    }
                }
            }
            table.Fields = GetFieldsByTable(table, sqlConnection);

            return table;
        }
        public static Table GetTableByTableID(int id)
        {
            Table table = new Table();

            using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
            {
                sqlConnection.Open();
                return GetTableByTableID(id, sqlConnection);
            }
        }
                public static Table GetTableWithRelatedFieldsByTableID(int id)
                {
                        Table table = new Table();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTableByTableID, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, id);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                sqlReader.Read();

                                                if (sqlReader.HasRows)
                                                {
                                                        table = new Table(sqlReader);
                                                        table.Fields = GetFieldsWithRelatedByTable(table);
                                                }
                                        }
                                }
                        }
                        return table;
                }
                public static Table GetTableWithRelatedFieldsByTableName(string tableName)
                {
                        Table table = new Table();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTableByTableName, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableName, tableName);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                sqlReader.Read();

                                                if (sqlReader.HasRows)
                                                {
                                                        table = new Table(sqlReader);
                                                        table.Fields = GetFieldsWithRelatedByTable(table);
                                                }
                                        }
                                }
                        }
                        return table;
                }
                public static bool AddTable(Table table, TableGroup tableGroup, string maskName, int subcategoryID, DynamicMask.DynamicMaskType? dynamicMaskType, string dynamicMaskName, bool addForeignKeyField, bool createDynemicMasks, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddTable, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, table.ID, System.Data.ParameterDirection.Output);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableName, table.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableGroupID, tableGroup.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableTypeID, table.TypeData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CustomTable, table.CustomTable);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_NeedsValidation, table.NeedsValidation);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MaskName, maskName);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SubcategoryID, subcategoryID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskName, dynamicMaskName);
                                                if (dynamicMaskType == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskTypeID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DynamicMaskTypeID, DynamicMask.DynamicMaskTypesDictionary[(DynamicMask.DynamicMaskType)dynamicMaskType].ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddForeignKeyField, (addForeignKeyField) ? 1 : 0);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CreateDynamicMask, createDynemicMasks);
                                                
                                                sqlCommand.ExecuteNonQuery();

                                                table.ID = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_TableID].Value.ToString());
                                        }
                                }
                returnMessage = RAIS.Common.Messages.TABLE_ADDED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool RemoveTable(Table table, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteTable, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, table.ID);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.TABLE_REMOVED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
        public static Fields GetFieldsByTable(Table table)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();
                return GetFieldsByTable(table, sqlConnection);
                        }
                }
        public static Fields GetFieldsByTable(Table table, SqlConnection sqlConnection)
        {
            Fields fields = new Fields();
            using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFieldsByTableID, sqlConnection))
            {
                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, table.ID);

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        Field field = new Field(sqlReader);
                        field.Table = table;
                        fields.Add(field.Name, field);
                    }
                }
            }
            return fields;
        }
                public static Fields GetFieldsWithRelatedByTable(Table table)
                {
                        Fields fields = new Fields();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFieldsByTableID, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, table.ID);
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_WithRelatedFields, 1);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Field field = new Field(sqlReader);
                            field.Table=table;
                                                        fields.Add(field.Name, field);
                                                }
                                        }
                                }
                        }

                        return fields;
                }
                public static Field GetFieldByID(int fieldID)
                {
                        Field field = null;

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFieldByID, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldID, fieldID);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                sqlReader.Read();

                                                if (sqlReader.HasRows)
                                                {
                                                        field = new Field(sqlReader);
                                                        if (field.RelatedTable != null)
                                                                field.RelatedTable.Fields = GetFieldsByTable(field.RelatedTable);
                                                }
                                        }
                                }
                        }

                        return field;
                }
        public static bool AddField(Table table, Field field, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddField, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldID, field.ID, System.Data.ParameterDirection.Output);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldName, field.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_LocalLanguageFieldName, field.LocalLanguageFieldName);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldPurpose, field.FieldPurpose);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_LocalLanguageFieldPurpose, field.LocalLanguageFieldPurpose);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, table.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldTypeID, field.TypeData.ID);
                        if (field.SizeData == null)
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldSizeID, null);
                        else
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldSizeID, field.SizeData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_NecessityTypeID, field.NecessityData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Order, field.Order);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_IsNameField, field.IsNameField);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SystemField, field.SystemField);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_VisibleOnForms, field.VisibleOnForms);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UniqueValue, field.UniqueValue);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_NeedsTranslation, field.NeedsTranslation);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Multiline, field.Multiline);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_HighField, field.HighField);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ReadOnly, field.ReadOnly);
                                                if (field.RelatedTable == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedTableID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedTableID, field.RelatedTable.ID);

                                                sqlCommand.ExecuteNonQuery();

                                                field.ID = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_FieldID].Value.ToString());
                                        }
                                }
                returnMessage = RAIS.Common.Messages.TABLE_FIELD_ADDED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool SaveField(Field field, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateField, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldID, field.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldName, field.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_LocalLanguageFieldName, field.LocalLanguageFieldName);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldPurpose, field.FieldPurpose);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_LocalLanguageFieldPurpose, field.LocalLanguageFieldPurpose);
                                                if (field.SizeData == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldSizeID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldSizeID, field.SizeData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_NecessityTypeID, field.NecessityData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_IsNameField, field.IsNameField);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SystemField, field.SystemField);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_VisibleOnForms, field.VisibleOnForms);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UniqueValue, field.UniqueValue);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_NeedsTranslation, field.NeedsTranslation);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Multiline, field.Multiline);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_HighField, field.HighField);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ReadOnly, field.ReadOnly);
                                                if (field.RelatedTable == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedTableID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedTableID, field.RelatedTable.ID);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.TABLE_FIELD_SAVED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                if (excep is System.Data.SqlClient.SqlException)
                                {
                                        if (((SqlException)excep).Number == 515)
                                                returnMessage = RAIS.Common.Messages.WRONG_NECESSITY_TYPE;
                                }
                                return false;
                        }
                }
                public static bool SaveFieldOrder(Field field, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateFieldOrder, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldID, field.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Order, field.Order);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.TABLE_FIELD_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool SaveFieldQueryName(Field field, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateFieldQueryName, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldID, field.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, field.QueryName);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.TABLE_FIELD_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool RemoveField(Field field, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteField, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FieldID, field.ID);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                returnMessage = RAIS.Common.Messages.TABLE_FIELD_REMOVED;
                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static FieldSizes GetFieldSizesByFieldType(Field.FieldType fieldType)
                {
                        FieldSizes fieldSizes = new FieldSizes();

                        foreach (KeyValuePair<string, Field.FieldSizeData> fieldSizePair in Field.FieldSizesDictionary)
                                if (fieldSizePair.Value.FieldType == fieldType)
                                        fieldSizes.Add(fieldSizePair.Key, fieldSizePair.Value);

                        return fieldSizes;
                }
                public static XmlDocument GetTableAndFieldXml()
                {
                        XmlDocument xmlDoc = null;

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GenerateTablesScript, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                sqlReader.Read();

                                                if (sqlReader.HasRows)
                                                {
                                                        xmlDoc = new XmlDocument();
                                                        xmlDoc.LoadXml("<Tables>" + sqlReader[0].ToString() + "</Tables>");

                                                }
                                        }
                                }
                        }
                        return xmlDoc;
                }
        public static Queries GetQueries()
                {
            return GetQueries(null);
                }
                public static Query GetQueryByQueryID(int id)
                {
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();
                return GetQueryByQueryID(id, sqlConnection);
                        }
                }

        public static Query GetQueryByQueryID(int id, SqlConnection sqlConnection)
        {
            Query query = new Query();

            using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueryByQueryID, sqlConnection))
            {
                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, id);

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                {
                    sqlReader.Read();

                    if (sqlReader.HasRows)
                    {
                        query = new Query(sqlReader);
                    }
                }
            }

            query.QueryParameters = GetQueryParametersByQuery(query);
            return query;
        }

                public static Query GetQueryByQueryName(string queryName)
                {
                        Query query = new Query();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueryByQueryName, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, queryName);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                sqlReader.Read();

                                                if (sqlReader.HasRows)
                                                {
                                                        query = new Query(sqlReader);
                                                        query.QueryParameters = GetQueryParametersByQuery(query);
                                                }
                                        }
                                }
                        }
                        return query;
                }
                public static Queries GetQueriesByQueryType(Query.QueryType queryType)
                {
                        Query.QueryTypeData queryTypeData = Query.GetQueryTypeDataByQueryType(queryType);
            return GetQueries(queryTypeData);
                }
                public static List<string> GetQueryAssignedObjectsNames(Query query)
                {
                        List<string> assignedObjectsNames = new List<string>();

            if (query.Type == Query.QueryType.DataRoleRestriction)
            {
                assignedObjectsNames = DataAccessLayer.DataRoleRestrictionManagement.GetQueryAssignedRestrictions(query.Name);
            }
            else
            {
                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                {
                    sqlConnection.Open();

                    using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueryAssignedObjects, sqlConnection))
                    {
                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID);

                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                        {
                            while (sqlReader.Read())
                            {
                                string objectName = sqlReader["Assigned Object Name"].ToString();
                                if (!assignedObjectsNames.Contains(objectName))
                                    assignedObjectsNames.Add(objectName);
                            }
                        }
                    }
                }
            }

                        return assignedObjectsNames;
                }
                public static bool AddQuery(Query query, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddQuery, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID, System.Data.ParameterDirection.Output);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, query.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryText, query.QueryText);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryTypeID, query.TypeData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParametersXML, query.NewParameterNamesInXml);

                                                sqlCommand.ExecuteNonQuery();

                                                query.ID = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_QueryID].Value.ToString());
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool SaveQuery(Query query, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateQuery, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryName, query.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryText, query.QueryText);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryTypeID, query.TypeData.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParametersXML, query.NewParameterNamesInXml);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool RemoveQuery(Query query, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteQuery, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_REMOVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool CreateQuery(Query query, ref string returnMessage)
                {
                        bool success = true;
                        DataSet dataSet = new DataSet();
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(string.Empty, DataAccessUtilities.ConnectionString);
                        StringBuilder sqlStr = new StringBuilder();

                        if (query != null)
                        {
                                if (query.Type == Query.QueryType.RanAutogeneration)
                                {
                                        success = CreateStoredProcedure(query, dataSet, dataAdapter, ref returnMessage);
                                }
                                else
                                {
                                        success = ValidateForbiddenCommandsInQuery(query, dataSet, dataAdapter, ref returnMessage);

                                        if (success)
                                        {
                                                // Use test query execution to get the result record set
                                                // Use this record set for columns definition during function creation
                                                // Add DECLARE for each parameter
                                                bool firstParameter = true;
                                                foreach (KeyValuePair<string, QueryParameter> kvp in query.QueryParameters)
                                                {
                                                        QueryParameter parameter = kvp.Value;
                                                        sqlStr.AppendLine(string.Format("{0} @{1} {2}"
                                                                , (firstParameter) ? "DECLARE" : "      ,"
                                                                , parameter.Name
                                                                , GetSqlTypeByParameterType(parameter.Type)));
                                                        firstParameter = false;
                                                }

                                                // Add SET for each parameter
                                                firstParameter = true;
                                                foreach (KeyValuePair<string, QueryParameter> kvp in query.QueryParameters)
                                                {
                                                        QueryParameter parameter = kvp.Value;
                                                        sqlStr.AppendLine(string.Format("SET @{0} = {1}"
                                                                , parameter.Name
                                                                , GetParameterValueByParameterType(parameter.Type)));
                                                        firstParameter = false;
                                                }

                                                // Add QueryText
                                                sqlStr.AppendLine(string.Empty);
                                                sqlStr.AppendLine(query.QueryText);

                                                //SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlStr.ToString(), DataAccessUtilities.ConnectionString);
                                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                                try
                                                {
                                                        dataAdapter.Fill(dataSet);
                                                }
                                                catch (Exception e)
                                                {
                                                        success = false;
                            returnMessage = e.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                                }

                                                if (success)
                                                {
                                                        sqlStr.Length = 0;
                                                        try
                                                        {
                                                                // Drop function if exists
                                                                sqlStr.AppendLine(string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))", query.Name));
                                                                sqlStr.AppendLine(string.Format("       DROP FUNCTION [dbo].[{0}]", query.Name));
                                                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                                                dataAdapter.Fill(dataSet);

                                                                // Create function
                                                                sqlStr.Length = 0;
                                                                sqlStr.AppendLine(string.Format("/******************************************************************************"));
                                                                sqlStr.AppendLine(string.Format("**  Function Name: [{0}]", query.Name));
                                                                sqlStr.AppendLine(string.Format("******************************************************************************/"));
                                                                sqlStr.AppendLine(string.Format("CREATE Function [{0}](", query.Name));

                                                                // Add parameters

                                sqlStr.AppendLine(CreateParametersBlock(query));

                                                                sqlStr.AppendLine(string.Format(")"));
                                                                sqlStr.AppendLine(string.Format("RETURNS @ResultTable TABLE"));
                                                                sqlStr.AppendLine(string.Format("("));

                                                                // Add return fields declaration
                                                                bool firstColumn = true;
                                                                foreach (DataColumn column in dataSet.Tables[0].Columns)
                                                                {
                                                                        sqlStr.AppendLine(string.Format("       {0}[{1}] {2}"
                                                                                , (firstColumn) ? "  " : ", "
                                                                                , column.ColumnName
                                                                                , GetSqlTypeByColumnType(column.DataType.ToString())));

                                                                        firstColumn = false;
                                                                }

                                                                sqlStr.AppendLine(string.Format(")"));
                                                                sqlStr.AppendLine(string.Format("AS"));
                                                                sqlStr.AppendLine(string.Format("BEGIN"));

                                                                sqlStr.AppendLine(string.Format("       INSERT @ResultTable"));
                                                                sqlStr.AppendLine(string.Format("       {0}", query.QueryText));
                                                                sqlStr.AppendLine(string.Format(""));
                                                                sqlStr.AppendLine(string.Format("       RETURN"));
                                                                sqlStr.AppendLine(string.Format("END"));

                                                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                                                dataAdapter.Fill(dataSet);

                                                                // Grant select for public
                                                                sqlStr.Length = 0;
                                                                sqlStr.AppendLine(string.Format("GRANT SELECT ON [{0}] TO PUBLIC", query.Name));
                                                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                                                dataAdapter.Fill(dataSet);

                                                                success = SaveQuery_SetCompiled(query, true, ref returnMessage);

                                                                if (success)
                                                                        returnMessage = Common.Messages.QUERY_CREATED;
                                                        }
                                                        catch (Exception e)
                                                        {
                                                                success = false;
                                returnMessage = e.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                                        }
                                                }
                                        }
                                }
                        }
                        return success;
                }
                public static bool CheckQuery(Query query, ref string returnMessage)
                {
                        bool success = true;
                        DataSet dataSet = new DataSet();
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(query.QueryText, DataAccessUtilities.ConnectionString);
                        try
                        {
                                dataAdapter.Fill(dataSet);

                                returnMessage = Common.Messages.QUERY_IS_VALID;
                        }
                        catch (Exception e)
                        {
                                success = false;
                returnMessage = e.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                        }

                        return success;
                }
        public static QueryParameters GetQueryParametersByQuery(Query query, SqlConnection sqlConnection)
        {
            QueryParameters queryParameters = new QueryParameters();
            using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueryParametersByQueryID, sqlConnection))
            {
                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID);

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        QueryParameter queryParameter = new QueryParameter(sqlReader);
                        queryParameters.Add(queryParameter.Name, queryParameter);
                    }
                }

                foreach (var queryParameter in queryParameters.Values)
                {
                    if (queryParameter.Table != null)
                        queryParameter.Table = GetTableByTableID(queryParameter.Table.ID, sqlConnection);
                    if (queryParameter.RelatedQuery != null)
                        queryParameter.RelatedQuery = GetQueryByQueryID(queryParameter.RelatedQuery.ID);
                }
            }

            return queryParameters;
        }

                public static QueryParameters GetQueryParametersByQuery(Query query)
                {
                        QueryParameters queryParameters = new QueryParameters();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                return GetQueryParametersByQuery(query, sqlConnection);
                        }
                }
                public static bool AddQueryParameter(Query query, QueryParameter queryParameter, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddQueryParameter, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterID, queryParameter.ID, System.Data.ParameterDirection.Output);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterName, queryParameter.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterTypeID, queryParameter.TypeData.ID);
                                                if (queryParameter.Table == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, queryParameter.Table.ID);
                                                if (queryParameter.RelatedQuery == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedQueryID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedQueryID, queryParameter.RelatedQuery.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Order, queryParameter.Order);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_VisibleName, queryParameter.VisibleName);

                                                sqlCommand.ExecuteNonQuery();

                                                queryParameter.ID = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_QueryParameterID].Value.ToString());
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_PARAMETER_ADDED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool SaveQueryParameter(QueryParameter queryParameter, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateQueryParameter, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterID, queryParameter.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterName, queryParameter.Name);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterTypeID, queryParameter.TypeData.ID);
                                                if (queryParameter.Table == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, queryParameter.Table.ID);
                                                if (queryParameter.RelatedQuery == null)
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedQueryID, null);
                                                else
                                                        DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RelatedQueryID, queryParameter.RelatedQuery.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Order, queryParameter.Order);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_VisibleName, queryParameter.VisibleName);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_PARAMETER_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool SaveQueryParameterOrder(QueryParameter queryParameter, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateQueryParameterOrder, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterID, queryParameter.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Order, queryParameter.Order);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_PARAMETER_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool RemoveQueryParameter(QueryParameter queryParameter, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteQueryParameter, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryParameterID, queryParameter.ID);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_PARAMETER_REMOVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }
                public static bool CreateHistoryTable(Table table, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_CreateHistoryTable, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_TableID, table.ID);

                                                sqlCommand.ExecuteNonQuery();
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.HISTORY_TABLE_ADDED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }

        public static bool IsTableContainData(Table table)
        {
            string query="select count(*) as [Count] from [{0}]";
            int count = 0;
            string tabName = table.Name;
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                {
                    sqlConnection.Open();

                    for (int i = 0; i < 2; i++)
                    {
                        using (SqlCommand sqlCommand = new SqlCommand(string.Format(query, table.Name), sqlConnection))
                        {
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                reader.Read();
                                count += (int)reader["Count"];
                            }
                        }
                    }
                    tabName = table.Name.Substring(0, table.Name.Length - 8);
                }
            }
            catch (Exception excep)
            {
                return true;
            }
            return count > 0;
        }
                #endregion
                //*********************************************************************
                #region Private static helper functions

                static TableTypes GetTableTypes()
                {
                        TableTypes tableTypes = new TableTypes();
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTableTypes, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Table.TableTypeData tableTypeData = new Table.TableTypeData(sqlReader);
                                                        
                                                        Table.TableType tableType;
                                                        try
                                                        {
                                                                // Next operator throw an exeption if we do not have 
                                                                // element in enum which corresponds to table type in Table Type table
                                                                // Catch it and throw exception with more clear message.
                                                                tableType = tableTypeData.Type;
                                                        }
                                                        catch
                                                        {
                                                                throw new Exception("Table type " + tableTypeData.VisibleName + " exists in DB but does not exist in Enum Table.TableType. The running ASP server is not up to date with respect to the DB.");
                                                        }

                                                        tableTypes.Add(tableTypeData.VisibleName, tableTypeData);
                                                }

                                                // We checked that all enum elements exist in [Table Type] table, 
                                                // so throw an exeption if number of elements in enum does not 
                                                // equal number of elements in [Table Type] table
                                                if (Enum.GetNames(typeof(Table.TableType)).Length != tableTypes.Count)
                                                        throw new Exception("There are table types which exist in Enum Table.TableType but does not exist in DB. The running DB is not up to date with respect to the ASP server.");
                                        }
                                }
                        }

                        return tableTypes;
                }
                static FieldTypes GetFieldTypes()
                {
                        FieldTypes fieldTypes = new FieldTypes();
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFieldTypes, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Field.FieldTypeData fieldTypeData = new Field.FieldTypeData(sqlReader);

                                                        Field.FieldType fieldType;
                                                        try
                                                        {
                                                                // Next operator throw an exeption if we do not have 
                                                                // element in enum which corresponds to field type in Field Type table
                                                                // Catch it and throw exception with more clear message.
                                                                fieldType = fieldTypeData.Type;
                                                        }
                                                        catch
                                                        {
                                                                throw new Exception("Field type " + fieldTypeData.VisibleName + " exists in DB but does not exist in Enum Field.FieldType. The running ASP server is not up to date with respect to the DB.");
                                                        }

                                                        fieldTypes.Add(fieldTypeData.VisibleName, fieldTypeData);
                                                }

                                                // We checked that all enum elements exist in [Field Type] table, 
                                                // so throw an exeption if number of elements in enum does not 
                                                // equal number of elements in [Field Type] table
                                                if (Enum.GetNames(typeof(Field.FieldType)).Length != fieldTypes.Count)
                                                        throw new Exception("There are field types which exist in Enum Field.FieldType but does not exist in DB. The running DB is not up to date with respect to the ASP server.");
                                        }
                                }
                        }

                        return fieldTypes;
                }
                static FieldSizes GetFieldSizes()
                {
                        FieldSizes fieldSizes = new FieldSizes();
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFieldSizes, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Field.FieldSizeData fieldSizeData = new Field.FieldSizeData(sqlReader);

                                                        Field.FieldSize fieldSize;
                                                        try
                                                        {
                                                                // Next operator throw an exeption if we do not have 
                                                                // element in enum which corresponds to field size in Field Size table
                                                                // Catch it and throw exception with more clear message.
                                                                fieldSize = fieldSizeData.Type;
                                                        }
                                                        catch
                                                        {
                                                                throw new Exception("Field size " + fieldSizeData.VisibleName + " exists in DB but does not exist in Enum Field.FieldSize. The running ASP server is not up to date with respect to the DB.");
                                                        }

                                                        fieldSizes.Add(fieldSizeData.VisibleName, fieldSizeData);
                                                }

                                                // We checked that all enum elements exist in [Field Size] table, 
                                                // so throw an exeption if number of elements in enum does not 
                                                // equal number of elements in [Field Size] table
                                                if (Enum.GetNames(typeof(Field.FieldSize)).Length != fieldSizes.Count)
                                                        throw new Exception("There are field sizes which exist in Enum Field.FieldSize but does not exist in DB. The running DB is not up to date with respect to the ASP server.");
                                        }
                                }
                        }

                        return fieldSizes;
                }
                static NecessityTypes GetNecessityTypes()
                {
                        NecessityTypes necessityTypes = new NecessityTypes();
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetNecessityTypes, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Field.NecessityTypeData necessityTypeData = new Field.NecessityTypeData(sqlReader);

                                                        Field.NecessityType necessityType;
                                                        try
                                                        {
                                                                // Next operator throw an exeption if we do not have 
                                                                // element in enum which corresponds to necessity type in Necessity Type table
                                                                // Catch it and throw exception with more clear message.
                                                                necessityType = necessityTypeData.Type;
                                                        }
                                                        catch
                                                        {
                                                                throw new Exception("Necessity type " + necessityTypeData.VisibleName + " exists in DB but does not exist in Enum Field.NecessityType. The running ASP server is not up to date with respect to the DB.");
                                                        }

                                                        necessityTypes.Add(necessityTypeData.VisibleName, necessityTypeData);
                                                }

                                                // We checked that all enum elements exist in [Necessity Type] table, 
                                                // so throw an exeption if number of elements in enum does not 
                                                // equal number of elements in [Necessity Type] table
                                                if (Enum.GetNames(typeof(Field.NecessityType)).Length != necessityTypes.Count)
                                                        throw new Exception("There are necessity types which exist in Enum Field.NecessityType but does not exist in DB. The running DB is not up to date with respect to the ASP server.");
                                        }
                                }
                        }

                        return necessityTypes;
                }
                static TableGroups GetTableGroups()
                {
                        TableGroups tableGroups = new TableGroups();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetTableGroups, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        TableGroup tableGroup = new TableGroup(sqlReader);
                                                        tableGroups.Add(tableGroup.Name, tableGroup);
                                                }
                                        }
                                }
                        }

                        return tableGroups;
                }
                static QueryTypes GetQueryTypes()
                {
                        QueryTypes queryTypes = new QueryTypes();
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();


                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueryTypes, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Query.QueryTypeData queryTypeData = new Query.QueryTypeData(sqlReader);

                                                        Query.QueryType queryType;
                                                        try
                                                        {
                                                                // Next operator throw an exeption if we do not have 
                                                                // element in enum which corresponds to query parameter type in [Query Type] table
                                                                // Catch it and throw exception with more clear message.
                                                                queryType = queryTypeData.Type;
                                                        }
                                                        catch
                                                        {
                                                                throw new Exception("Query type " + queryTypeData.VisibleName + " exists in DB but does not exist in Enum Query.QueryType. The running ASP server is not up to date with respect to the DB.");
                                                        }

                                                        queryTypes.Add(queryTypeData.VisibleName, queryTypeData);
                                                }

                                                // We checked that all enum elements exist in [Query Type] table, 
                                                // so throw an exeption if number of elements in enum does not 
                                                // equal number of elements in [Query Type] table
                                                if (Enum.GetNames(typeof(Query.QueryType)).Length != queryTypes.Count)
                                                        throw new Exception("There are query types which exist in Enum Query.QueryType but does not exist in DB. The running DB is not up to date with respect to the ASP server.");
                                        }
                                }
                        }

                        return queryTypes;
                }
                static QueryParameterTypes GetQueryParameterTypes()
                {
                        QueryParameterTypes queryParameterTypes = new QueryParameterTypes();
                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueryParameterTypes, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        QueryParameter.QueryParameterTypeData queryParameterTypeData = new QueryParameter.QueryParameterTypeData(sqlReader);

                                                        QueryParameter.QueryParameterType queryParameterType;
                                                        try
                                                        {
                                                                // Next operator throw an exeption if we do not have 
                                                                // element in enum which corresponds to query parameter type in [Query Parameter Type] table
                                                                // Catch it and throw exception with more clear message.
                                                                queryParameterType = queryParameterTypeData.Type;
                                                        }
                                                        catch
                                                        {
                                                                throw new Exception("Query parameter type " + queryParameterTypeData.VisibleName + " exists in DB but does not exist in Enum QueryParameter.QueryParameterType. The running ASP server is not up to date with respect to the DB.");
                                                        }

                                                        queryParameterTypes.Add(queryParameterTypeData.VisibleName, queryParameterTypeData);
                                                }

                                                // We checked that all enum elements exist in [Query Parameter Type] table, 
                                                // so throw an exeption if number of elements in enum does not 
                                                // equal number of elements in [Query Parameter Type] table
                                                if (Enum.GetNames(typeof(QueryParameter.QueryParameterType)).Length != queryParameterTypes.Count)
                                                        throw new Exception("There are query parameter types which exist in Enum QueryParameter.QueryParameterType but does not exist in DB. The running DB is not up to date with respect to the ASP server.");
                                        }
                                }
                        }

                        return queryParameterTypes;
                }
        static Queries GetQueries(Query.QueryTypeData queryTypeData)
                {
                        Queries queries = new Queries();

                        using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                        {
                                sqlConnection.Open();

                                using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetQueries, sqlConnection))
                                {
                                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                        if (queryTypeData != null)
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryTypeID, queryTypeData.ID);

                                        using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                                        {
                                                while (sqlReader.Read())
                                                {
                                                        Query query = new Query(sqlReader);
                            //query.QueryParameters = GetQueryParametersByQuery(query);
                                                        queries.Add(query.Name, query);
                                                }
                                        }
                                }
                foreach (var query in queries.Values)
                    query.QueryParameters = GetQueryParametersByQuery(query, sqlConnection);
                        }
                        return queries;
                }

                static string GetSqlTypeByParameterType(QueryParameter.QueryParameterType type)
                {
                        string sqlType = string.Empty;
                        switch (type)
                        {
                                case QueryParameter.QueryParameterType.Integer:
                                case QueryParameter.QueryParameterType.Lookup:
                                case QueryParameter.QueryParameterType.LookupFromTable:
                                case QueryParameter.QueryParameterType.LookupFromQuery:
                                case QueryParameter.QueryParameterType.PrimaryKey:
                                case QueryParameter.QueryParameterType.PreselectedFacility:
                                case QueryParameter.QueryParameterType.PreselectedDepartment:
                                case QueryParameter.QueryParameterType.UserID:
                                        sqlType = "int";
                                        break;
                                case QueryParameter.QueryParameterType.Text:
                                        sqlType = "nvarchar(max)";
                                        break;
                                case QueryParameter.QueryParameterType.Date:
                                case QueryParameter.QueryParameterType.DatesRangeEndDate:
                                case QueryParameter.QueryParameterType.DatesRangeStartDate:
                                        sqlType = "datetime";
                                        break;
                                case QueryParameter.QueryParameterType.Rational:
                                        sqlType = "float";
                                        break;
                                case QueryParameter.QueryParameterType.Xml:
                                        sqlType = "xml";
                                        break;
                                default:
                                        sqlType = "sql_variant";
                                        break;
                        }
                        return sqlType;
                }

                static string GetParameterValueByParameterType(QueryParameter.QueryParameterType type)
                {
                        string parameterValue = string.Empty;
                        switch (type)
                        {
                                case QueryParameter.QueryParameterType.Integer:
                                case QueryParameter.QueryParameterType.Lookup:
                                case QueryParameter.QueryParameterType.LookupFromTable:
                                case QueryParameter.QueryParameterType.LookupFromQuery:
                                case QueryParameter.QueryParameterType.PrimaryKey:
                                case QueryParameter.QueryParameterType.Rational:
                                case QueryParameter.QueryParameterType.PreselectedFacility:
                                case QueryParameter.QueryParameterType.PreselectedDepartment:
                                case QueryParameter.QueryParameterType.UserID:
                                        parameterValue = 0.ToString();
                                        break;
                                case QueryParameter.QueryParameterType.Text:
                                case QueryParameter.QueryParameterType.Xml:
                                        parameterValue = "''";
                                        break;
                                case QueryParameter.QueryParameterType.Date:
                                case QueryParameter.QueryParameterType.DatesRangeEndDate:
                                case QueryParameter.QueryParameterType.DatesRangeStartDate:
                                        //parameterValue = string.Format("'{0}'", DateTime.Now.ToString());
                                        parameterValue = "GETDATE()";
                                        break;
                                default:
                                        parameterValue = "null";
                                        break;
                        }
                        return parameterValue;
                }

                static string GetSqlTypeByColumnType(string type)
                {
                        string sqlType = string.Empty;
                        switch (type)
                        {
                                case "System.Int32":
                                        sqlType = "int";
                                        break;
                                case "System.String":
                                        sqlType = "nvarchar(max)";
                                        break;
                                case "System.DateTime":
                                        sqlType = "datetime";
                                        break;
                                case "System.Single":
                                        sqlType = "real";
                                        break;
                                case "System.Double":
                                        sqlType = "float";
                                        break;
                                default:
                                        sqlType = "sql_variant";
                                        break;
                        }
                        return sqlType;
                }

                static bool ValidateForbiddenCommandsInQuery(Query query, DataSet dataSet, SqlDataAdapter dataAdapter, ref string returnMessage)
                {
                        bool success = true;
                        StringBuilder sqlStr = new StringBuilder();
                        try
                        {
                                // Drop function if exists
                                sqlStr.AppendLine(string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))", query.Name));
                                sqlStr.AppendLine(string.Format("       DROP FUNCTION [dbo].[{0}]", query.Name));
                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                dataAdapter.Fill(dataSet);

                                // Create function
                                sqlStr.Length = 0;
                                sqlStr.AppendLine(string.Format("/******************************************************************************"));
                                sqlStr.AppendLine(string.Format("**  Function Name: [{0}]", query.Name));
                                sqlStr.AppendLine(string.Format("******************************************************************************/"));
                                sqlStr.AppendLine(string.Format("CREATE Function [{0}](", query.Name));
                                sqlStr.AppendLine(string.Format(")"));
                                sqlStr.AppendLine(string.Format("RETURNS @ResultTable TABLE"));
                                sqlStr.AppendLine(string.Format("("));
                                sqlStr.AppendLine(string.Format("       [TestColumn] [int] NOT NULL"));
                                sqlStr.AppendLine(string.Format(")"));
                                sqlStr.AppendLine(string.Format("AS"));
                                sqlStr.AppendLine(string.Format("BEGIN"));
                                sqlStr.AppendLine(string.Format("       {0}", query.QueryText));
                                sqlStr.AppendLine(string.Format("       INSERT @ResultTable"));
                                sqlStr.AppendLine(string.Format("       SELECT 0"));
                                sqlStr.AppendLine(string.Format(""));
                                sqlStr.AppendLine(string.Format("       RETURN"));
                                sqlStr.AppendLine(string.Format("END"));

                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                dataAdapter.Fill(dataSet);

                                returnMessage = string.Empty;
                        }
                        catch(SqlException excep)
                        {
                                foreach (SqlError err in excep.Errors)
                                {
                                        if (err.Number == 443)
                                        {
                                                success = false;
                                                returnMessage = Common.Messages.QUERY_ERROR_FORBIDDEN_STATEMENTS;
                                                break;
                                        }
                                }
                        }
                        catch (Exception e)
                        {
                                success = false;
                                returnMessage = e.Message;
                        }
                        return success;
                }
                static bool SaveQuery_SetCompiled(Query query, bool compiled, ref string returnMessage)
                {
                        try
                        {
                                using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
                                {
                                        sqlConnection.Open();

                                        using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateQueryCompiled, sqlConnection))
                                        {
                                                sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_QueryID, query.ID);
                                                DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Compiled, compiled);

                                                sqlCommand.ExecuteNonQuery();
                                                query.Compiled = compiled;
                                        }
                                }
                                returnMessage = RAIS.Common.Messages.QUERY_SAVED;
                                return true;
                        }
                        catch (Exception excep)
                        {
                returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
                                return false;
                        }
                }

                static bool CreateStoredProcedure(Query query, DataSet dataSet, SqlDataAdapter dataAdapter, ref string returnMessage)
                {
                        StringBuilder sqlStr = new StringBuilder();
                        bool success = true;
                        try
                        {
                                // Drop function if exists
                                sqlStr.AppendLine(string.Format("IF  EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '{0}')", query.Name));
                                sqlStr.AppendLine(string.Format("       DROP PROCEDURE [dbo].[{0}]", query.Name));
                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                dataAdapter.Fill(dataSet);

                                // Create function
                                sqlStr.Length = 0;
                                sqlStr.AppendLine(string.Format("/******************************************************************************"));
                                sqlStr.AppendLine(string.Format("**  Stored Procedure Name: [{0}]", query.Name));
                                sqlStr.AppendLine(string.Format("******************************************************************************/"));
                                sqlStr.AppendLine(string.Format("CREATE Procedure [{0}]", query.Name));

                sqlStr.AppendLine(CreateParametersBlock(query));

                                sqlStr.AppendLine(string.Format("AS"));
                                sqlStr.AppendLine(string.Format("       {0}", query.QueryText));

                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                dataAdapter.Fill(dataSet);

                                // Grant select for public
                                sqlStr.Length = 0;
                                sqlStr.AppendLine(string.Format("GRANT EXEC ON [{0}] TO PUBLIC", query.Name));
                                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                dataAdapter.Fill(dataSet);

                                // Test Stored Procedure
                sqlStr.Length = 0;
                sqlStr.Append(string.Format("EXEC [{0}]", query.Name));

                for (int i = 0; i < query.QueryParameters.Count; i++)
                    if(i==query.QueryParameters.Count-1)
                        sqlStr.Append("0");
                    else
                        sqlStr.Append("0,");


                dataAdapter.SelectCommand.CommandText = sqlStr.ToString();
                                dataAdapter.Fill(dataSet);

                                success = SaveQuery_SetCompiled(query, true, ref returnMessage);

                                if (success)
                                        returnMessage = Common.Messages.QUERY_CREATED;
                        }
                        catch (Exception e)
                        {
                                success = false;
                returnMessage = e.Message;
                        }

                        return success;
                }

        private static string CreateParametersBlock(Query query)
        {
            bool firstParameter = true;
            StringBuilder sqlStr=new StringBuilder();
            foreach (KeyValuePair<string, QueryParameter> kvp in query.QueryParameters)
            {
                QueryParameter parameter = kvp.Value;
                sqlStr.AppendLine(string.Format("{0} @{1} {2}"
                    , (firstParameter) ? "        " : " , "
                    , parameter.Name
                    , GetSqlTypeByParameterType(parameter.Type)));
                firstParameter = false;
            }
            return sqlStr.ToString();
        }
                #endregion
        }
}